package com.sto.stoapp.db.manager;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.sto.stoapp.db.StoDataBase;
import com.sto.stoapp.db.constant.StoCastSonstant;
import com.sto.stoapp.entity.BaseEntity;
import com.sto.stoapp.entity.DownCastEntity;
import com.sto.stoapp.entity.FlightCompareEntity;
import com.sto.stoapp.log.ExceptionLog;
import com.sto.stoapp.util.UnifiedStaticType;

/**
 * 到包预报表管理
 * @author mgf
 *
 */
@SuppressLint("SimpleDateFormat")
public class CastManager extends StoDataBase {

    private static CastManager manager;

    private static final String SQL_QUERY_ALL = "select * from "
            + TAB_FORE_CAST + " where 1=1";

    /** 查询记录总数 */
    private static final String SQL_QUERY_COUNT = "select count(*) from "
            + TAB_FORE_CAST + " where 1=1";

    /** 按单号查询记录 */
    private static final String SQL_QUERY_BY_CODE = "select * from "
            + TAB_FORE_CAST + " where " + CAST_BAR_CODE + " = ? " + " and "
            + CAST_SCAN_DATE + " like ? ";

    /** 按航班号查询当天应到记录 */
    private static final String SQL_QUERY_COUNT_BY_FLIGHT = "select count(*) from "
            + TAB_FORE_CAST
            + " where "
            + CAST_FLIGHT
            + " = ? "
            + " and "
            + CAST_SCAN_DATE + " like ? ";

    /** 按时间查询某一航班的应到记录数 */
    private static final String SQL_QUERY_FLIGHT_COUNT_BY_DATE = "select count(*), "
            + CAST_FLIGHT
            + " from "
            + TAB_FORE_CAST
            + " where "
            + CAST_SCAN_DATE + " like ? " + " group by " + CAST_FLIGHT;

    /** 根据时间查询指定航班下的记录 */
    private static final String SQL_QUERY_BY_DATE = " select * from "
            + TAB_FORE_CAST + " where " + CAST_SCAN_DATE + " like ? " + " and "
            + CAST_FLIGHT + " like ? ";

    private CastManager(Context context) {
        super(context);
    }

    /**
     * 单例化对象
     * 
     * @param context
     * @return
     */
    public static CastManager getInstance(Context context) {
        synchronized (UnifiedStaticType.DATABASE_SYNCHRONIZED) {
            if (manager == null) {
                manager = new CastManager(context);
            }
            return manager;
        }
    }

    /**
     * 插入单条数据
     * 
     * @param entity
     * @return
     */
    public boolean insert(BaseEntity entity) {
        synchronized (UnifiedStaticType.DATABASE_SYNCHRONIZED) {
            long res = -1;
            DownCastEntity castEntity = null;
            if (entity instanceof DownCastEntity) {
                castEntity = (DownCastEntity) entity;
            } else {
                return false;
            }
            try {
                ContentValues values = createContentValues(castEntity);
                res = getSqliteDatabase(UnifiedStaticType.WRITE_DATABASE)
                        .insert(TAB_FORE_CAST, null, values);
                if (res > 0) {
                    Log.d("插入一条到包预报表记录", "成功");
                } else {
                    Log.d("插入一条到包预报表记录", "失败");
                }
            } catch (Exception e) {
                e.printStackTrace();
                ExceptionLog.writeLog("insert", e.getMessage());
            } finally {
                closeDb(UnifiedStaticType.WRITE_DATABASE);
            }
            return res > 0 ? true : false;
        }
    }

    private ContentValues createContentValues(DownCastEntity castEntity) {
        ContentValues values = new ContentValues();
        values.put(CAST_ROUTE_CODE, castEntity.getRouteCode());
        values.put(CAST_BAR_CODE, castEntity.getBarCode());
        values.put(CAST_SCAN_DATE, castEntity.getScanDate());
        values.put(CAST_SCAN_TIME, castEntity.getScanTime());
        values.put(CAST_SCAN_MAN, castEntity.getSalesName());
        values.put(CAST_SCAN_SITE, castEntity.getSiteName());
        values.put(CAST_FLIGHT, castEntity.getFlight());
        values.put(CAST_INC_TIME, castEntity.getIncTime());
        values.put(CAST_FILE_NAME, castEntity.getFileName());
        values.put(CAST_REMARK, castEntity.getRemark());
        return values;
    }

    /**
     * 删除该数据库表所有数据
     * 
     * @return
     */
    public boolean deleteAll() {
        synchronized (UnifiedStaticType.DATABASE_SYNCHRONIZED) {
            long res = -1;
            try {
                res = getSqliteDatabase(UnifiedStaticType.WRITE_DATABASE)
                        .delete(TAB_FORE_CAST, null, null);
            } catch (Exception e) {
                e.printStackTrace();
                ExceptionLog.writeLog("deleteAll", e.getMessage());
            } finally {
                closeDb(UnifiedStaticType.WRITE_DATABASE);
            }
            return res > 0 ? true : false;
        }
    }

    /**
     * 删除指定日期之前的数据
     * @param date扫描日期
     * @return 删除成功返回true
     */
    public boolean deleteByDate(String date) {
        synchronized (UnifiedStaticType.DATABASE_SYNCHRONIZED) {
            long res = -1;
            try {
                res = getSqliteDatabase(UnifiedStaticType.WRITE_DATABASE)
                        .delete(TAB_FORE_CAST,
                                StoCastSonstant.CAST_SCAN_DATE + " <= ? ",
                                new String[] { date });
            } catch (Exception e) {
                e.printStackTrace();
                ExceptionLog.writeLog("delete_by_date", e.getMessage());
            } finally {
                closeDb(UnifiedStaticType.WRITE_DATABASE);
            }
            return res > 0 ? true : false;
        }
    }

    /**
     * 批量更新数据
     * 
     * @param datas
     */
    public void insertList(List<BaseEntity> datas) {
        synchronized (UnifiedStaticType.DATABASE_SYNCHRONIZED) {
            int length = datas.size();
            SQLiteDatabase db = getSqliteDatabase(UnifiedStaticType.WRITE_DATABASE);
            try {
                db.beginTransaction();
                for (int i = 0; i < length; i++) {
                    long res = -1;
                    DownCastEntity castEntity = null;
                    if (datas.get(i) instanceof DownCastEntity) {
                        castEntity = (DownCastEntity) datas.get(i);
                    } else {
                        continue;
                    }
                    ContentValues values = createContentValues(castEntity);
                    res = db.insert(TAB_FORE_CAST, null, values);
                    if (res > 0) {
                        Log.d("插入一条到包预报表记录", "成功");
                    } else {
                        Log.d("插入一条到包预报表记录", "失败");
                    }
                }
            } finally {
                datas.clear();
                db.setTransactionSuccessful();
                db.endTransaction();
                closeDb(UnifiedStaticType.WRITE_DATABASE);
            }
        }
    }

    /**
     * 按时间查询航班以及应到数量
     * @param date 扫描时间 
     * @return 
     */
    public List<FlightCompareEntity> queryFlightAndCountByDate(String date) {
        synchronized (UnifiedStaticType.DATABASE_SYNCHRONIZED) {
            List<FlightCompareEntity> result = new ArrayList<FlightCompareEntity>();
            Cursor cursor = getSqliteDatabase(UnifiedStaticType.READ_DATABASE)
                    .rawQuery(SQL_QUERY_FLIGHT_COUNT_BY_DATE,
                            new String[] { date });
            if (cursor != null) {
                if (cursor.moveToFirst()) {
                    do {
                        if (cursor.getString(1) != null
                                && cursor.getString(1).length() > 0) {
                            FlightCompareEntity entity = new FlightCompareEntity();
                            entity.setFlight(cursor.getString(1));
                            entity.setNeedCount(cursor.getInt(0) + "");
                            entity.setDate(date);
                            result.add(entity);
                        } else {
                            return null;
                        }
                    } while (cursor.moveToNext());
                }
                closeCursor(cursor);
            }
            closeDb(UnifiedStaticType.READ_DATABASE);
            return result;
        }
    }

    /**
     * 按时间查询航班下的所有记录
     * @param date 扫描时间 
     * @return 
     */
    public List<DownCastEntity> queryRecordByDate(String date, String flight) {
        synchronized (UnifiedStaticType.DATABASE_SYNCHRONIZED) {
            Cursor c = getCursor(UnifiedStaticType.READ_DATABASE,
                    SQL_QUERY_BY_DATE, new String[] { date, flight });
            List<DownCastEntity> data = getDataFromCursor(c);
            return data;
        }
    }

    /**
     * 查询路由表记录条数
     * 
     * @return
     */
    public int queryAllCount() {
        synchronized (UnifiedStaticType.DATABASE_SYNCHRONIZED) {
            int count = 0;
            Cursor cursor = getSqliteDatabase(UnifiedStaticType.READ_DATABASE)
                    .rawQuery(SQL_QUERY_COUNT, null);
            if (cursor != null && cursor.moveToFirst()) {
                count = cursor.getInt(0);
            }
            closeCursor(cursor);
            closeDb(UnifiedStaticType.READ_DATABASE);
            return count;
        }
    }

    /**
     * 按照航班查询当天应到记录数
     * @param flight
     * @return
     */
    public int queryNeedCountByFlight(String flight) {
        synchronized (UnifiedStaticType.DATABASE_SYNCHRONIZED) {
            int count = 0;
            SimpleDateFormat sDateFormat = new SimpleDateFormat(
                    UnifiedStaticType.DATE_FORMAT);
            String date = sDateFormat.format(new java.util.Date());
            Cursor cursor = getSqliteDatabase(UnifiedStaticType.READ_DATABASE)
                    .rawQuery(SQL_QUERY_COUNT_BY_FLIGHT,
                            new String[] { flight, date});
            if (cursor != null && cursor.moveToFirst()) {
                count = cursor.getInt(0);
            }
            closeCursor(cursor);
            closeDb(UnifiedStaticType.READ_DATABASE);
            return count;
        }
    }

    /**
     * 查询所有数据
     * 
     * @return
     */
    public List<DownCastEntity> queryAll() {
        synchronized (UnifiedStaticType.DATABASE_SYNCHRONIZED) {
            List<DownCastEntity> data = getDataFromCursor(getSqliteDatabase(
                    UnifiedStaticType.READ_DATABASE).rawQuery(SQL_QUERY_ALL,
                    null));
            return data;
        }
    }

    private List<DownCastEntity> getDataFromCursor(final Cursor c) {
        List<DownCastEntity> lstData = new ArrayList<DownCastEntity>();
        if (c != null) {
            if (c.moveToFirst()) {
                do {
                    lstData.add(getSingleData(c));
                } while (c.moveToNext());
            }
            closeCursor(c);
        }
        closeDb(UnifiedStaticType.READ_DATABASE);
        return lstData;
    }

    /**
     * 根据包号查找当天的记录
     * @return
     */
    public DownCastEntity queryByBarCode(String barCode) {
        synchronized (UnifiedStaticType.DATABASE_SYNCHRONIZED) {
            DownCastEntity entity = null;
            SimpleDateFormat sDateFormat = new SimpleDateFormat(
                    UnifiedStaticType.DATE_FORMAT);
            String date = sDateFormat.format(new java.util.Date());
            Cursor c = getCursor(UnifiedStaticType.READ_DATABASE,
                    SQL_QUERY_BY_CODE, new String[] { barCode, date });
            if (c != null && c.moveToFirst()) {
                entity = getSingleData(c);
            }
            closeCursor(c);
            closeDb(UnifiedStaticType.READ_DATABASE);
            return entity;
        }
    }

    private DownCastEntity getSingleData(Cursor c) {
        DownCastEntity data = new DownCastEntity();
        data.setRouteCode(c.getString(c.getColumnIndex(CAST_ROUTE_CODE)));
        data.setBarCode(c.getString(c.getColumnIndex(CAST_BAR_CODE)));
        data.setScanDate(c.getString(c.getColumnIndex(CAST_SCAN_DATE)));
        data.setScanTime(c.getString(c.getColumnIndex(CAST_SCAN_TIME)));
        data.setSalesName(c.getString(c.getColumnIndex(CAST_SCAN_MAN)));
        data.setSiteName(c.getString(c.getColumnIndex(CAST_SCAN_SITE)));
        data.setFlight(c.getString(c.getColumnIndex(CAST_FLIGHT)));
        data.setIncTime(c.getString(c.getColumnIndex(CAST_INC_TIME)));
        data.setFileName(c.getString(c.getColumnIndex(CAST_FILE_NAME)));
        data.setRemark(c.getString(c.getColumnIndex(CAST_REMARK)));
        return data;
    }

}
